#!/usr/bin/perl -w
# Show innodb  status
# @param $1 install home
# @param $2 delimiter
# @param $3 host
# @param $4 port
# @param $5 username
# @param $6 password
#
use strict;
my $SQL="show innodb status\\G";
my ($MYSQL_HOME,$DELIM,$HOST,$PORT,$USER,$PASS) = @ARGV;
my $PARAM="";
$PARAM="$PARAM -h\"$HOST\"" if (defined($HOST) && $HOST ne "");
$PARAM="$PARAM -P\"$PORT\"" if (defined($PORT) && $PORT ne "");
$PARAM="$PARAM -u\"$USER\"" if (defined($USER) && $USER ne "");
$PARAM="$PARAM -p\"$PASS\"" if (defined($PASS) && $PASS ne "");
my $MYSQL="$MYSQL_HOME/bin/mysql";
my $MYSQLADMIN="$MYSQL_HOME/bin/mysqladmin";
if ($^O =~ m/MSWin32/) {
  $MYSQL =~ s/\//\\/g;
  $MYSQLADMIN =~ s/\//\\/g;
}
# Check if mysqld is alive
my $retmsg=`$MYSQLADMIN $PARAM ping 2>&1`;
chomp($retmsg);
if ( "$retmsg" ne "mysqld is alive" ) {
    print "em_error=Status${DELIM}Down\n";
    exit 1;
}
# Execute command and display the result as key-value pairs
my $TX_ID_COUNTER = 0;
my $TX_PURGE_DONE_FOR              = 0;
my $TX_PURGE_UNDO_FOR              = 0;
my @C=`$MYSQL -N -s $PARAM -e"$SQL"`;
chomp(@C);
foreach(@C) {

# Execute command and display the result as key-value pairs
#
# Trx id counter 1 70965145
# Purge done for trx's n:o < 1 70965084 undo n:o < 0 0
# History list length 36
#    
    if (/^Trx id counter\s(\d+)\s(\d+)/ms) {
         my $tx_id_hi32    = $1 * (2**32);
         my $tx_id_lo32    = $2;
         my $TX_ID_COUNTER = $tx_id_hi32 + $tx_id_lo32;
         print "em_result=TX_ID_COUNTER${DELIM}${TX_ID_COUNTER}\n";
    } elsif (/^Purge done for trx/) {
        my @a = split (/ /, $_);
        my $TX_PURGE_DONE_FOR = $a[6] * (2**32) + $a[7];
        my $TX_PURGE_UNDO_FOR = $a[11]* (2**32) + $a[12];
        print "em_result=TX_PURGE_DONE_FOR${DELIM}${TX_PURGE_DONE_FOR}\n";
        print "em_result=TX_PURGE_UNDO_FOR${DELIM}${TX_PURGE_UNDO_FOR}\n";
    } elsif (/^History list length\s(\d+)/) {
        my $TX_HIS_LIST_LENGTH = $1;
        print "em_result=TX_HIS_LIST_LENGTH${DELIM}${TX_HIS_LIST_LENGTH}\n";
    }

#Total memory allocated 9443911338; in additional pool allocated 32478464
#Buffer pool size   512000
#Free buffers       27
#Database pages     506916
#Modified db pages  85564
#Pending reads 2
#Pending writes: LRU 0, flush list 0, single page 0
#Pages read 2048462437, created 5201905, written 285583927
#291.74 reads/s, 1.80 creates/s, 52.19 writes/s
#Buffer pool hit rate 950 / 1000
    if (/^Buffer pool size\s+(\d+)/) {
       my $Innodb_buffer_pool_pages_total = $1;
       print "em_result=Innodb_buffer_pool_pages_total|${Innodb_buffer_pool_pages_total}\n";
    }
    
    if (/^Free buffers\s+(\d+)/) {
       my $Innodb_buffer_pool_pages_free = $1;
       print "em_result=Innodb_buffer_pool_pages_free|${Innodb_buffer_pool_pages_free}\n";
    }

    if (/^Database pages\s+(\d+)/) {
       my $Innodb_buffer_pool_pages_data = $1;
       print "em_result=Innodb_buffer_pool_pages_data|${Innodb_buffer_pool_pages_data}\n";
    }
   
    if (/^Modified db pages\s+(\d+)/) {
       my $Innodb_buffer_pool_pages_dirty = $1;
       print "em_result=Innodb_buffer_pool_pages_dirty|${Innodb_buffer_pool_pages_dirty}\n";
    }
   
    if (/^Buffer pool hit rate/) {
       my @bphr = split / /,$_;
       my $Innodb_buffer_read_hit_ratio = $bphr[4] * 1.0 / $bphr[6] * 100.0;
       print "em_result=Innodb_buffer_read_hit_ratio|${Innodb_buffer_read_hit_ratio}\n";
    }
    elsif (/^No buffer pool page gets/) {
       my $Innodb_buffer_read_hit_ratio = 100*1.0;
       print "em_result=Innodb_buffer_read_hit_ratio|${Innodb_buffer_read_hit_ratio}\n";
    }
    
    if (/^Pending reads\s(\d+)/) {
      #print "pending\n";
      #$Innodb_data_pending_reads = $1;
      my $Innodb_buffer_pool_pending_read = $1;
      print "em_result=Innodb_buffer_pool_pending_read|${Innodb_buffer_pool_pending_read}\n";
    }

    if (/^Pending writes:\s+LRU\s+(\d+),\s+flush list\s+(\d+),\s+single page\s+(\d+)/) {
       #print "pending write $1 $2 $3\n";
       #my @pw = split / /,$_;
       #$Innodb_data_pending_writes = substr($pw[2],0,index($pw[2],",")) + substr($pw[5],0,index($pw[5],",")) + $pw[8];
       #$Innodb_data_pending_writes = $1 + $2 + $3;
       my $Innodb_buffer_pool_pending_write_lru        = $1;
       my $Innodb_buffer_pool_pending_write_flushlist  = $2;
       my $Innodb_buffer_pool_pending_write_singlepage = $3;
       print "em_result=Innodb_buffer_pool_pending_write_lru|${Innodb_buffer_pool_pending_write_lru}\n";
       print "em_result=Innodb_buffer_pool_pending_write_flushlist|${Innodb_buffer_pool_pending_write_flushlist}\n";
       print "em_result=Innodb_buffer_pool_pending_write_singlepage|${Innodb_buffer_pool_pending_write_singlepage}\n";
    }

    if (/^Pages read\s(\d+),\screated\s(\d+),\swritten\s(\d+)/) {
       #print "pages read\n";
       my $Innodb_pages_read    = $1;
       my $Innodb_pages_created = $2;
       my $Innodb_pages_written = $3;
       print "em_result=Innodb_pages_read|${Innodb_pages_read}\n";
       print "em_result=Innodb_pages_created|${Innodb_pages_created}\n";
       print "em_result=Innodb_pages_written|${Innodb_pages_written}\n";
    }

#----------
#SEMAPHORES
#----------
#OS WAIT ARRAY INFO: reservation count 147295, signal count 147269
#Mutex spin waits 0, rounds 375970, OS waits 7998
#RW-shared spins 271532, OS waits 134752; RW-excl spins 4215, OS waits 4083
   if (/^OS WAIT ARRAY INFO.*count\s+(\d+),\s+signal count\s+(\d+)/) {
      print "em_result=Innodb_sem_os_wait_reservation_count|$1\n";
      print "em_result=Innodb_sem_os_wait_signal_count|$2\n";
   }
 
   if (/^Mutex spin waits\s+(\d+),\s+rounds\s+(\d+),\s+OS waits\s+(\d+)/) {
      print "em_result=Innodb_sem_mutex_spin_waits|$1\n";
      print "em_result=Innodb_sem_mutex_rounds|$2\n";
      print "em_result=Innodb_sem_mutex_os_waits|$3\n";  
   }

   if (/^RW-shared spins\s+(\d+),\s+OS waits\s+(\d+);\s+RW-excl spins\s+(\d+),\s+OS waits\s+(\d+)/) {
      print "em_result=Innodb_sem_read_write_shared_locks_total|$1\n";
      print "em_result=Innodb_sem_read_write_shared_locks_via_os_wait|$2\n";
      print "em_result=Innodb_sem_read_write_exclusive_locks_total|$3\n";
      print "em_result=Innodb_sem_read_write_exclusive_locks_via_os_wait|$4\n";
   }
   
#-------------------------------------
#INSERT BUFFER AND ADAPTIVE HASH INDEX
#-------------------------------------
#Ibuf for space 0: size 1, free list len 38, seg size 40, is empty
#Ibuf: size 1, free list len 17, seg size 19,
#2310 inserts, 2310 merged recs, 572 merges
#Hash table size 553253, used cells 0, node heap has 1 buffer(s)
#Hash table size 4620037, node heap has 63 buffer(s)
#0.00 hash searches/s, 0.00 non-hash searches/s
    if (/^Ibuf:\s+size\s+(\d+),\s+free list len\s+(\d+),\s+seg size\s+(\d+),/) {
       my $Innodb_ibuf_used_pages = $1;
       my $Innodb_ibuf_free_pages = $2;
       my $Innodb_ibuf_total_pages = $3;
       print "em_result=Innodb_ibuf_used_pages|${Innodb_ibuf_used_pages}\n";
       print "em_result=Innodb_ibuf_free_pages|${Innodb_ibuf_free_pages}\n";
       print "em_result=Innodb_ibuf_total_pages|${Innodb_ibuf_total_pages}\n";
    }
    elsif (/^Ibuf for space 0:\s+size\s+(\d+),\s+free list len\s+(\d+),\s+seg size\s+(\d+),\s+is empty/) {
       my $Innodb_ibuf_used_pages = $1;
       my $Innodb_ibuf_free_pages = $2;
       my $Innodb_ibuf_total_pages = $3;
       print "em_result=Innodb_ibuf_used_pages|${Innodb_ibuf_used_pages}\n";
       print "em_result=Innodb_ibuf_free_pages|${Innodb_ibuf_free_pages}\n";
       print "em_result=Innodb_ibuf_total_pages|${Innodb_ibuf_total_pages}\n";
    }
    
    if (/^Hash table size\s+(\d+), node heap has (\d+) buffer/) {
       my $Innodb_adaptive_hash_index_size = $1;
       my $Innodb_adaptive_hash_index_node_heaps = $2;
       print "em_result=Innodb_adaptive_hash_index_size|${Innodb_adaptive_hash_index_size}\n";
       print "em_result=Innodb_adaptive_hash_index_node_heaps|${Innodb_adaptive_hash_index_node_heaps}\n";
    }
    elsif (/^Hash table size (\d+), used cells \d+, node heap has (\d+) buffer/) {
       my $Innodb_adaptive_hash_index_size = $1;
       my $Innodb_adaptive_hash_index_node_heaps = $2;
       print "em_result=Innodb_adaptive_hash_index_size|${Innodb_adaptive_hash_index_size}\n";
       print "em_result=Innodb_adaptive_hash_index_node_heaps|${Innodb_adaptive_hash_index_node_heaps}\n";
    }
    
    if (/^(\d+\.\d+) hash searches\/s, (\d+\.\d+) non-hash searches/) {
       my $Innodb_hash_searches_ps = $1;
       my $Innodb_non_hash_searches_ps = $2;
       print "em_result=Innodb_hash_searches_ps|${Innodb_hash_searches_ps}\n";
       print "em_result=Innodb_non_hash_searches_ps|${Innodb_non_hash_searches_ps}\n";
    }
  
#--------------
#ROW OPERATIONS
#--------------
#8 queries inside InnoDB, 125 queries in queue
#198 read views open inside InnoDB
#Main thread id 9, state: purging
#Number of rows inserted 17211, updated 51774, deleted 17251, read 7072253
#53.00 inserts/s, 158.40 updates/s, 53.00 deletes/s, 21425.21 reads/s 
    if (/^Number of rows inserted\s(\d+),\s+updated\s+(\d+),\sdeleted\s+(\d+),\s+read\s+(\d+)/) {
       my $Innodb_rows_inserted = $1;
       my $Innodb_rows_updated  = $2;
       my $Innodb_rows_deleted  = $3;
       my $Innodb_rows_read     = $4;
       print "em_result=Innodb_rows_inserted|${Innodb_rows_inserted}\n";
       print "em_result=Innodb_rows_updated|${Innodb_rows_updated}\n";
       print "em_result=Innodb_rows_deleted|${Innodb_rows_deleted}\n";
       print "em_result=Innodb_rows_read|${Innodb_rows_read}\n";
    }
    
    if (/^(\d+)\s+queries inside InnoDB,\s+(\d+)\s+queries in queue/) {
       my $Innodb_queries_in_kernel = $1;
       my $Innodb_queries_in_queue      = $2;
       print "em_result=Innodb_queries_in_kernel|${Innodb_queries_in_kernel}\n";
       print "em_result=Innodb_queries_in_queue|${Innodb_queries_in_queue}\n";
    }

    if (/^(\d+)\s+read views open inside InnoDB/) {
        my $Innodb_consistent_reads = $1;
        print "em_result=Innodb_consistent_reads|${Innodb_consistent_reads}\n"
    }

#--------
#FILE I/O
#--------
#I/O thread 0 state: waiting for i/o request (insert buffer thread)
#I/O thread 1 state: waiting for i/o request (log thread)
#I/O thread 2 state: waiting for i/o request (read thread)
#I/O thread 3 state: waiting for i/o request (write thread)
#Pending normal aio reads: 0, aio writes: 0,
# ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
#Pending flushes (fsync) log: 0; buffer pool: 0
#1651892 OS file reads, 569463 OS file writes, 124585 OS fsyncs
#1726.33 reads/s, 16806 avg bytes/read, 603.97 writes/s, 130.95 fsyncs/s
    if (/^(\d+)\s+OS file reads,\s+(\d+)\s+OS file writes,\s(\d+)/) {
       my $Innodb_os_file_reads  = $1;
       my $Innodb_os_file_writes = $2;
       my $Innodb_os_fsyncs      = $3;
       print "em_result=Innodb_os_file_reads|${Innodb_os_file_reads}\n";
       print "em_result=Innodb_os_file_writes|${Innodb_os_file_writes}\n";
       print "em_result=Innodb_os_fsyncs|${Innodb_os_fsyncs}\n";
    }
    
    if (/^Pending normal aio reads:\s+(\d+),\s+aio writes:\s+(\d+)/) {
       #print "test:$1,$2,$3\n";
       my $Innodb_os_pending_aio_reads = $1;
       my $Innodb_os_pending_aio_writes =$2;
       print "em_result=Innodb_os_pending_aio_reads|${Innodb_os_pending_aio_reads}\n";
       print "em_result=Innodb_os_pending_aio_writes|${Innodb_os_pending_aio_writes}\n";
    }

    if (/^\s+ibuf aio reads:\s+(\d+),\s+log i\/o's:\s+(\d+),\s+sync i\/o's:\s+(\d+)/) {
       my $Innodb_os_pending_ibuf_aio_reads = $1;
       my $Innodb_os_pending_logios   = $2;
       my $Innodb_os_pending_syncios  = $3;
       print "em_result=Innodb_os_pending_ibuf_aio_reads|${Innodb_os_pending_ibuf_aio_reads}\n";
       print "em_result=Innodb_os_pending_logios|${Innodb_os_pending_logios}\n";
       print "em_result=Innodb_os_pending_syncios|${Innodb_os_pending_syncios}\n";
    }
 
    if (/^Pending\s+flushes\s+\(fsync\)\s+log:\s+(\d+);\s+buffer\s+pool:\s+(\d+)/) {
       my $Innodb_data_pending_fsyncs   = $2;
       my $Innodb_os_log_pending_fsyncs = $1;
       print "em_result=Innodb_data_pending_fsyncs|${Innodb_data_pending_fsyncs}\n";
       print "em_result=Innodb_os_log_pending_fsyncs|${Innodb_os_log_pending_fsyncs}\n";
    }

    if (/(\d+)\s+avg bytes\/read,/) {
       my $Innodb_os_avg_bytes_ps_read = $1;
       print "em_result=Innodb_os_avg_bytes_ps_read|${Innodb_os_avg_bytes_ps_read}\n";
    }    
#---
#LOG
#---
#Log sequence number 10 862924565
#Log flushed up to   10 862924565
#Last checkpoint at  10 862883954
#0 pending log writes, 0 pending chkp writes
#2143832 log i/o's done, 5.80 log i/o's/second
 
    if (/^Log sequence number\s+(\d+)\s+(\d+)/) {
    #   my $tempnum = 2**32;
    #   print  "test it \n";
    #   print  "lsn $tempnum\n";
       my $Innodb_log_lsn_curr = $1 * (2**32) + $2;
       print "em_result=Innodb_log_lsn_curr|${Innodb_log_lsn_curr}\n";
    }
  
    if (/^Log flushed up to\s+(\d+)\s+(\d+)/) {
       my $Innodb_log_lsn_flush = $1 * (2**32) + $2;
       print "em_result=Innodb_log_lsn_flush|${Innodb_log_lsn_flush}\n";
    }

    if (/^Last checkpoint at\s+(\d+)\s+(\d+)/) {
       
       my $Innodb_log_lsn_checkpoint = $1 * (2**32) + $2;
       print  "em_result=Innodb_log_lsn_checkpoint|${Innodb_log_lsn_checkpoint}\n";
    }

    if (/^(\d+)\s+pending log writes,\s+(\d+)\s+pending chkp writes/) {
    #if (\d+\s+pending log writes/) {
       #print "TEST:$_\n";
       my $Innodb_os_log_pending_log_writes  = $1;
       my $Innodb_os_log_pending_chkp_writes = $2;
       print "em_result=Innodb_os_log_pending_log_writes|${Innodb_os_log_pending_log_writes}\n";
       print "em_result=Innodb_os_log_pending_chkp_writes|${Innodb_os_log_pending_chkp_writes}\n";
    }

    if (/^(\d+)\s+log i\/o's done,/) {
       my $Innodb_os_log_ios = $1;
       print "em_result=Innodb_os_log_ios|${Innodb_os_log_ios}\n";
    }
    
}
#print "em_result=TX_ID_COUNTER${DELIM}${TX_ID_COUNTER}\n";
#print "em_result=TX_PURGE_DONE_FOR${DELIM}${TX_PURGE_DONE_FOR}\n";
#print "em_result=TX_PURGE_UNDO_FOR${DELIM}${TX_PURGE_UNDO_FOR}\n";
#print "em_result=TX_HIS_LIST_LENGTH${DELIM}${TX_HIS_LIST_LENGTH}\n";
#print "em_result=Innodb_buffer_pool_pages_total|${Innodb_buffer_pool_pages_total}\n";
#print "em_result=Innodb_buffer_pool_pages_free|${Innodb_buffer_pool_pages_free}\n";
#print "em_result=Innodb_buffer_pool_pages_data|${Innodb_buffer_pool_pages_data}\n";
#print "em_result=Innodb_buffer_pool_pages_dirty|${Innodb_buffer_pool_pages_dirty}\n";
#print "em_result=Innodb_buffer_read_hit_ratio|${Innodb_buffer_read_hit_ratio}\n";
#print "em_result=Innodb_data_pending_reads|${Innodb_data_pending_reads}\n";
#print "em_result=Innodb_pages_read|${Innodb_pages_read}\n";
#print "em_result=Innodb_pages_created|${Innodb_pages_created}\n";
#print "em_result=Innodb_pages_written|${Innodb_pages_written}\n";
#print "em_result=Innodb_rows_inserted|${Innodb_rows_inserted}\n";
#print "em_result=Innodb_rows_updated|${Innodb_rows_updated}\n";
#print "em_result=Innodb_rows_deleted|${Innodb_rows_deleted}\n";
#print "em_result=Innodb_rows_read|${Innodb_rows_read}\n";
#print "em_result=Innodb_os_file_reads|${Innodb_os_file_reads}\n";
#print "em_result=Innodb_os_file_writes|${Innodb_os_file_writes}\n";
#print "em_result=Innodb_os_fsyncs|${Innodb_os_fsyncs}\n";
exit 0;

